-- for Oracle 9i+

drop table allcomments.sys_user_m cascade constraints;
drop table allcomments.sys_user_d cascade constraints;
drop table allcomments.user_tag cascade constraints;
drop table allcomments.comments cascade constraints;

create table allcomments.sys_user_m
(
  user_id  nvarchar2(50) primary key,
  password nvarchar2(32) not null,
  role_id  number not null
);

create table allcomments.sys_user_d
(
  user_id  nvarchar2(50) primary key,
  nickname  nvarchar2(50),
  realname  nvarchar2(50),
  sex number,
  age number,
  location nvarchar2(200),
  question nvarchar2(50),
  answer nvarchar2(50),
  mobile nvarchar2(20),
  email_contact nvarchar2(100),
  email_contact_visibility number,
  email_secret nvarchar2(100)
);

create table allcomments.user_tag
(
  user_id nvarchar2(50),
  tag     nvarchar2(32),
  visibility number not null
);

alter table allcomments.user_tag
  add constraint pk_user_tag primary key (user_id, tag);

create table allcomments.comments
(
  comment_id    char(100) primary key,
  pre_comment_id char(100) not null,
  url    nvarchar2(500) not null,
  post_time timestamp default SYSTIMESTAMP not null,,
  content   nvarchar2(2000) not null,
  user_id   nvarchar2(50) not null,
  ip    nvarchar2(50) not null,
  tags      nvarchar2(500)
)
